Snowflakeに取り込んだJSONデータをクエリして展開したい
2024.06.05データアナリティクス事業本部の荒木です。
Snowflakeに取り込んだJSONデータの各項目をSQLで簡単にテーブルに展開することができとても便利だったのでご紹介します。
本題
SnowflakeではVARIANT型に定義されたカラムに、JSONのような構造データをインポートし 取り込んだ半構造化データをクエリすることで構造データから必要な項目を展開することができます。
テーブル作成
まず、VARIANT型で定義されたテーブルを作成し構造データをインポートします。 今回は公式ページ記載のサンプルデータを使用します。
CREATE OR REPLACE TABLE TEST_SCHEMA.TEST_TABLE
(
json_data variant
)
AS
SELECT PARSE_JSON(column1) AS json_data
FROM VALUES
('{
"date" : "2017-04-28",
"dealership" : "Valley View Auto Sales",
"salesperson" : {
"id": "55",
"name": "Frank Beasley"
},
"customer" : [
{"name": "Joyce Ridgely", "phone": "16504378889", "address": "San Francisco, CA"}
],
"vehicle" : [
{"make": "Honda", "model": "Civic", "year": "2017", "price": "20275", "extras":["ext warranty", "paint protection"]}
]
}'),
('{
"date" : "2017-04-28",
"dealership" : "Tindel Toyota",
"salesperson" : {
"id": "274",
"name": "Greg Northrup"
},
"customer" : [
{"name": "Bradley Greenbloom", "phone": "12127593751", "address": "New York, NY"}
],
"vehicle" : [
{"make": "Toyota", "model": "Camry", "year": "2017", "price": "23500", "extras":["ext warranty", "rust proofing", "fabric protection"]}
]
}') v;
構造データをクエリしてデータを展開する
JSONデータを取り込んだテーブルをFROM句に指定し、クエリを実行することでデータを展開することができます。
以下にいくつかのパターンのクエリを記載します。
第一階層のデータ取得
構造データの第一階層のデータは、JSONのKEYをコロンで繋ぐことでVALUEを取得できます。
SELECT rj.json_data:customer AS col1 ,rj.json_data:date AS col2 ,rj.json_data:dealership AS col3 ,rj.json_data:salesperson AS col4 ,rj.json_data:vehicle AS col5 FROM TEST_SCHEMA.TEST_TABLE AS rj ;
| COL1 | COL2 | COL3 | COL4 | COL5 |
| --------------------------------------------------------------------------------- | ------------ | ----------------------- | ------------------------------------ | ----------------------------------------------------------------------------------------------------------------------------------- |
| [{"address": "San Francisco, CA","name": "Joyce Ridgely","phone": "16504378889"}] | "2017-04-28" | "Valley View Auto Sales"| {"id": "55","name": "Frank Beasley"} | [{"extras": ["ext warranty","paint protection"],"make": "Honda","model": "Civic","price": "20275","year": "2017"}] |
| [{"address": "New York, NY","name": "Bradley Greenbloom","phone": "12127593751"}] | "2017-04-28" | "Tindel Toyota" | {"id": "274","name": "Greg Northrup"}| [{"extras": ["ext warranty","rust proofing","fabric protection"],"make": "Toyota","model": "Camry","price": "23500","year": "2017"}]|
ネストされた階層構造のデータ取得
階層構造の値にさらに階層構造が含まれている場合、取得したいVALUEがある階層までの全てのKEYをコロンで繋ぐことでVALUEを取得できます。
SELECT rj.json_data:salesperson:id AS col1 ,rj.json_data:salesperson:name AS col2 FROM TEST_SCHEMA.TEST_TABLE AS rj ;
| COL1 | COL2 | | ---- | ------------- | | 55 | Frank Beasley | | 274 | Greg Northrup |
配列のデータ取得
階層構造の値が配列データの場合、FLATTEN関数を使用することで展開された配列の要素のインデックス番号を指定することで値を取得できます。
SELECT j0.value:extras[0] AS co1 ,j0.value:extras[1] AS co2 FROM TEST_SCHEMA.TEST_TABLE AS rj , LATERAL FLATTEN(INPUT => rj.json_data, OUTER => TRUE, PATH => 'vehicle') j0 ;
| COL1 | COL2 | | ------------ | ---------------- | | ext warranty | paint protection | | ext warranty | rust proofing |
また配列データの中に配列データが含まれている場合、FLATTEN関数で展開した値をさらに展開することで配列デー内の配列データの値も取得できます。
SELECT j1.value AS col1 FROM TEST_SCHEMA.TEST_TABLE AS rj , LATERAL FLATTEN(INPUT => rj.json_data, OUTER => TRUE, PATH => 'vehicle') j0 , LATERAL FLATTEN(INPUT => j0.value, OUTER => TRUE, PATH => 'extras') j1 ;
|COL1 | | ----------------- | | ext warranty | | paint protection | | ext warranty | | rust proofing | | fabric protection |
全ての項目を展開する
上記のクエリを使用して全てのJSONデータを展開する場合以下のようなクエリになります。
配列のデータの要素を行として展開する場合
配列データの要素を1カラムとして全ての要素を複数のレコードにして展開したい場合は、FLATTEN関数で展開した配列を再度FLATTEN関数で展開することで、配列内の配列データの要素を取得できます。
SELECT j0.value:address AS col1 ,j0.value:name AS col2 ,j0.value:phone AS col3 ,rj.json_data:date AS col4 ,rj.json_data:dealership AS col5 ,rj.json_data:salesperson:id AS col6 ,rj.json_data:salesperson:name AS col7 ,j2.value AS col8 ,j1.value:make AS col11 ,j1.value:model AS col12 ,j1.value:price AS col13 ,j1.value:year AS col14 FROM TEST_SCHEMA.TEST_TABLE AS rj , LATERAL FLATTEN(INPUT => rj.json_data, OUTER => TRUE, PATH => 'customer') j0 , LATERAL FLATTEN(INPUT => rj.json_data, OUTER => TRUE, PATH => 'vehicle') j1 , LATERAL FLATTEN(INPUT => j1.value, OUTER => TRUE, PATH => 'extras') j2 ;
| COL1 | COL2 | COL3 | COL4 | COL5 | COL6 | COL7 | COL8 | COL11 | COL12 | COL13 | COL14 | | ----------------- | ------------------ | ----------- | ---------- | ---------------------- | ---- | ------------- | ----------------- | ------ | ----- | ----- | ----- | | San Francisco, CA | Joyce Ridgely | 16504378889 | 2017-04-28 | Valley View Auto Sales | 55 | Frank Beasley | ext warranty | Honda | Civic | 20275 | 2017 | | San Francisco, CA | Joyce Ridgely | 16504378889 | 2017-04-28 | Valley View Auto Sales | 55 | Frank Beasley | paint protection | Honda | Civic | 20275 | 2017 | | New York, NY | Bradley Greenbloom | 12127593751 | 2017-04-28 | Tindel Toyota | 274 | Greg Northrup | ext warranty | Toyota | Camry | 23500 | 2017 | | New York, NY | Bradley Greenbloom | 12127593751 | 2017-04-28 | Tindel Toyota | 274 | Greg Northrup | rust proofing | Toyota | Camry | 23500 | 2017 | | New York, NY | Bradley Greenbloom | 12127593751 | 2017-04-28 | Tindel Toyota | 274 | Greg Northrup | fabric protection | Toyota | Camry | 23500 | 2017 |
配列のデータの要素を列として展開する場合
配列データの要素を1レコードのカラムとして全て展開する場合、配列データの要素を全てカラムとして展開するようにすることで取得できます。
SELECT j0.value:address AS col1 ,j0.value:name AS col2 ,j0.value:phone AS col3 ,rj.json_data:date AS col4 ,rj.json_data:dealership AS col5 ,rj.json_data:salesperson:id AS col6 ,rj.json_data:salesperson:name AS col7 ,j1.value:extras[0] AS col8 ,j1.value:extras[1] AS col9 ,j1.value:extras[2] AS col10 ,j1.value:make AS col11 ,j1.value:model AS col12 ,j1.value:price AS col13 ,j1.value:year AS col14 FROM TEST_SCHEMA.TEST_TABLE AS rj , LATERAL FLATTEN(INPUT => rj.json_data, OUTER => TRUE, PATH => 'customer') j0 , LATERAL FLATTEN(INPUT => rj.json_data, OUTER => TRUE, PATH => 'vehicle') j1
| COL1 | COL2 | COL3 | COL4 | COL5 | COL6 | COL7 | COL8 | COL9 | COL10 | COL11 | COL12 | COL13 | COL14 | COL4 | COL5 | | ----------------- | ------------------ | ----------- | ---------- | ---------------------- | ---- | ------------- | ------------ | ---------------- | ----------------- | ------ | ----- | ----- | ----- | ---------- | ---------------------- | | San Francisco, CA | Joyce Ridgely | 16504378889 | 2017-04-28 | Valley View Auto Sales | 55 | Frank Beasley | ext warranty | paint protection | | Honda | Civic | 20275 | 2017 | 2017-04-28 | Valley View Auto Sales | | New York, NY | Bradley Greenbloom | 12127593751 | 2017-04-28 | Tindel Toyota | 274 | Greg Northrup | ext warranty | rust proofing | fabric protection | Toyota | Camry | 23500 | 2017 | 2017-04-28 | Tindel Toyota |
FLATTEN関数で展開した配列を再度FLATTEN関数で展開した場合、インデックス番号を指定して値を取得するとNULLになってしまいうまく展開できませんでした。
ポイント
- 仮にクエリする項目がJSONにない場合には、クエリ結果はNULLで返してくれるためJSONデータ項目が必ずしも全て一致している必要はありません。
- より複雑な階層構造や配列などのデータが含まれているデータであれば、項目毎にテーブルを分けてデータを展開する設計をするのがいいのかなと思います。
まとめ
SnowflakeではJSONデータをクエリで展開することができたので、プログラムでJSONデータを展開する必要がなく簡単にデータ展開ができたのでとても助かりました。 構造データのデータ展開を検討されている方はぜひSnowflakeでのデータ展開をお試しください！
